package com.wny.server;

import java.sql.Connection;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 * @author Li 
 * Supply the core methods to operate database directly.
 * These methods are to be invoked by high-level class
 */

public class DBOperator {

	private DBConnector dbConnector = null;
	private Connection dbConn = null;
	private Statement selectStmt = null;
	private Statement updateStmt = null;
	private ResultSet dbResultSet = null;
	
	public DBOperator() {
		
	}
	
	public DBOperator(String host, String port, String dName, String uName,
			String pwd) {
		dbConnector = new DBConnector(host, port, dName, uName, pwd);

	}
	
	public boolean dbConnect() {
		dbConn = dbConnector.getdbConnection();
		if (dbConn == null)
			return false;
		else
			return true;
	}
	
	/**
	 * To execute select query.
	 * 
	 * @param selectSql
	 *            Select query to be executed
	 * @return ArrayList including StringBuilder. A record is a StringBuilder
	 */
	public ArrayList<String> dbSelect(String selectSql) {
		ArrayList<String> selectResult = new ArrayList<String>();
		StringBuilder recordInfo;
		try {
			selectStmt = dbConn.createStatement();
			dbResultSet = selectStmt.executeQuery(selectSql);

			ResultSetMetaData rsmd = dbResultSet.getMetaData();
			int colCount = rsmd.getColumnCount();

			while (dbResultSet.next()) {

				recordInfo = new StringBuilder();
				for (int i = 1; i <= colCount; i++) {

					// column starts with 1
					recordInfo.append(dbResultSet.getString(i));
					recordInfo.append(Constant.RESULT_DELIM_ATTRIBUTE);
				}
				//recordInfo.toString().trim();
				selectResult.add(recordInfo.toString());
			}
			dbResultSet.close();
			selectStmt.close();
		} catch (SQLException ex) {
			System.out.println(ex.getMessage());
			System.out.println(ex.getSQLState());
			System.out.println("Fail to select");
			System.out.println("Sql = " + selectSql);
		}
		return selectResult;
	}// end dbSelect()

	/**
	 * To execute update/delete/insert
	 * 
	 * @param sql
	 *            SQL query
	 * @return true for success, false for failure
	 */
	public boolean dbUpdate(String sql) {
		try {
			updateStmt = dbConn.createStatement();
			updateStmt.executeUpdate(sql);
			updateStmt.close();
			return true;
		} catch (SQLException ex) {
			System.out.println(ex.getMessage());
			System.out.println(ex.getSQLState());
			System.out.println("Fail to update");
			System.out.println("Sql = " + sql);
			return false;
		}
	}// end dbUpdate()
	
	public boolean dbClose() {
		return dbConnector.dbClose();
	}// end dbClose()
}
